OpenPyXL and Pandas for Excel Data Exchange

You want your MicroStation Python app to communicate with an external spreadsheet, probably Excel. One way to approach this is to work through an intermediate API, such as OpenPyXL or Pandas. The purpose of this article is to help you decide which API is best for your needs.

OpenPyXL and Pandas are installed with MicroStation Python. However, they serve different roles when working with Excel …

Library	Primary Role	Read/Write .xlsx	Best use with MicroStation	Limitations
pandas	Tabular data processing (DataFrame)	Yes	Filtering, joining, validating, grouping, and transforming Excel schedules or asset tables	Adds dependency and memory overhead
openpyxl	Native Python .xlsx workbook manipulation	Yes	Reading/writing specific worksheets, cells, styles, formulas, and named ranges	Less convenient than pandas for large-scale table transformations; no legacy .xls support

When to use OpenPyXL

Use it when your goal is to automate or preserve an Excel workbook.

• Read or write specific cells and worksheets.
• Apply formatting.
• Add formulas, tables, images, charts, or data validation.
• Update an existing .xlsx template.
• Preserve workbook layout and structure

When to use pandas

Use pandas for data-centric import/export. Choose it when Excel is acting as a data source rather than a formatted document …

• Thousands of rows of asset, survey, or coordinate records.
• Data cleaning: missing values, type conversion, duplicate detection.
• Matching Excel IDs to DGN element metadata.
• Aggregation and reporting by level, type, status, or area.
• Exporting result tables to a new worksheet/file.

pandas is particularly effective when you need operations such as filtering all records where Status == "New" or joining an Excel table to extracted DGN properties.

Examples

Examples are delivered with Python when you install MicroStation You should find them in folder

C:\ProgramData\Bentley\PowerplatformPython\Examples\MicroStation\Misc

• ExtractItemTypesToExcel
• RepopulateItemsFromExcel

Those examples use pandas to interface with an Excel file.

My Simple Example uses pandas to read a CSV or Excel file, and displays the data in a PyQt TableWidget.

Questions

Post questions to the MicroStation Programming Forum.